<?php 
/**
 *  Por Federico Pisarello - SisDev Software (c) 2012 - Buenos Aires, Argentina.
 * 	fpisarello@sisdevsoft.com
 */

require("includes/funciones.inc.php");

session_start();
if(!isset($_SESSION['userName']))
	exit();

function searchABMUsers()
{
	
	require("includes/constants.php");
	require("includes/datatables_db/Datatables.php");
	
	$datatables = new Datatables();
	
	$datatables
			->select('idUser, userName, userCompleteName, userEMail')
			->from('users')
			->where('userState <>', $stateErase)
			->join('typeuser', 'users.idTypeUser = typeuser.idTypeUser', 'inner')
			->select('description, latitude, longitude')
			->edit_column('latitude', '$1', 'transformLatLng(latitude)') // php functions
			->edit_column('longitude', '$1', 'transformLatLng(longitude)'); // php functions
		
	return $datatables->generate();
}

function transformLatLng($decimalVal)
{
	$signVal = 1;
			
	$valAbs = abs(round($decimalVal * 1000000.));
	$valDeg = (floor($valAbs / 1000000) * $signVal);		
	$valMin = floor((($valAbs/1000000) - floor($valAbs/1000000)) * 60);
	$valSeg = (floor((((($valAbs/1000000) - floor($valAbs/1000000)) * 60) - floor((($valAbs/1000000) - floor($valAbs/1000000)) * 60)) * 100000) *60/100000);
	
	return $valDeg.'&deg;'.$valMin.'\''.(int)$valSeg.'"';
}

function addUser()
{
	require("includes/constants.php");

	$DB = NewADOConnection('mysqlt');
	$DB->Connect();
	$DB->Execute("SET NAMES utf8;");
	//$DB->debug=true;

	$DB->StartTrans(); // start transaction
	
	$strSQL = "SELECT userName FROM users WHERE userName = '".$_REQUEST["userName"]."' AND userState <> '$stateErase'";
	$rsUsers = $DB->Execute($strSQL);		
	if(!$rsUsers->EOF)
	{
		$arrJSON->status = "ERROR";
		$arrJSON->msg = 'El nombre de usuario ingresado ya existe';
		return json_encode($arrJSON);
	}
	
	if(isset($_REQUEST["idUser"])){
		$strSQL = "SELECT userName FROM users WHERE idUser = ".$_REQUEST["idUser"];
		$rsUsers = $DB->Execute($strSQL);		
		
		if(!$rsUsers->EOF)
		{
			$arrJSON->status = "ERROR";
			$arrJSON->msg = 'El ID del usuario ingresado ya existe';
			return json_encode($arrJSON);
		}
	}
	
	//Latitud
	$latSign = 1;
	if($_REQUEST['latDeg'] < 0) 
		 $latSign = -1;
	$absdlat = abs(round($_REQUEST['latDeg'] * 1000000.));
	$absmlat = abs(round($_REQUEST['latMin'] * 1000000.)/1000000);
	$absslat = abs(round($_REQUEST['latSeg'] * 1000000.)/1000000);
	
	$absmlat = abs(round($absmlat * 1000000.));
	$absslat = abs(round($absslat * 1000000.)); 

	//Longitud
	$lngSign = 1;
	if($_REQUEST['lngDeg'] < 0) 
		 $lngSign = -1;
	$absdlng = abs(round($_REQUEST['lngDeg'] * 1000000.));
	$absmlng = abs(round($_REQUEST['lngMin'] * 1000000.)/1000000);
	$absslng = abs(round($_REQUEST['lngSeg'] * 1000000.)/1000000);
	
	$absmlng = abs(round($absmlng * 1000000.));
	$absslng = abs(round($absslng * 1000000.)); 

	
	$latitude = round($absdlat + ($absmlat/60.) + ($absslat/3600.) ) * $latSign/1000000;
	$longitude = round($absdlng + ($absmlng/60.) + ($absslng/3600.) ) * $lngSign/1000000;
	
	$strSQL = "INSERT INTO users ( ";
	if(isset($_REQUEST["idUser"]))
		$strSQL .= "	idUser, ";
	$strSQL .= "	userName, ";
	$strSQL .= "	userPassword, ";
	$strSQL .= "	userCompleteName, ";
	$strSQL .= "	userEMail, ";
	$strSQL .= "	userPhone, ";
	$strSQL .= "	userCellPhone, ";
	$strSQL .= "	userAddress, ";
	$strSQL .= "	userLocalidad, ";
	$strSQL .= "	userState, ";
	$strSQL .= "	idTypeUser, ";
	$strSQL .= "	latitude, ";
	$strSQL .= "	longitude ";
	$strSQL .= "	) VALUES ( ";
	if(isset($_REQUEST["idUser"]))
		$strSQL .= "	".$_REQUEST["idUser"].", ";
	$strSQL .= "	'".mb_strtoupper($_REQUEST["userName"], "UTF-8")."', ";
	$strSQL .= "	'".md5($_REQUEST["userPassword"])."', ";
	$strSQL .= "	'".mb_strtoupper($_REQUEST["userCompleteName"], "UTF-8")."', ";
	$strSQL .= "	'".$_REQUEST["userEMail"]."', ";
	$strSQL .= "	'".mb_strtoupper($_REQUEST["userPhone"], "UTF-8")."', ";
	$strSQL .= "	'".mb_strtoupper($_REQUEST["userCellPhone"], "UTF-8")."', ";
	$strSQL .= "	'".mb_strtoupper($_REQUEST["userAddress"], "UTF-8")."', ";
	$strSQL .= "	'".mb_strtoupper($_REQUEST["userLocalidad"], "UTF-8")."', ";
	$strSQL .= "	'".$stateAdd."', ";
	$strSQL .= "	".$_REQUEST["cmbTypeUser"].", ";
	$strSQL .= "	".$latitude.", ";
	$strSQL .= "	".$longitude;
	$strSQL .= "	)";
	
	$DB->Execute($strSQL);
	
	if (!$DB->CompleteTrans())
	{
		$arrJSON->status = "ERROR";
		$arrJSON->msg = 'Ocurri&oacute; un error al grabar el archivo';
	}
	else
	{
		$arrJSON->status = "OK";
		$arrJSON->msg = 'El registro se guard&oacute; correctamente';
	}	
	return json_encode($arrJSON);
}

function updateUser()
{
	require("includes/constants.php");

	$DB = NewADOConnection('mysqlt');
	$DB->Connect();
	$DB->Execute("SET NAMES utf8;");
	//$DB->debug=true;
	
		//Latitud
	$latSign = 1;
	if($_REQUEST['latDeg'] < 0) 
		 $latSign = 1;
	$absdlat = abs(round($_REQUEST['latDeg'] * 1000000.));
	$absmlat = abs(round($_REQUEST['latMin'] * 1000000.)/1000000);
	$absslat = abs(round($_REQUEST['latSeg'] * 1000000.)/1000000);
	
	$absmlat = abs(round($absmlat * 1000000.));
	$absslat = abs(round($absslat * 1000000.)); 

	//Longitud
	$lngSign = 1;
	if($_REQUEST['lngDeg'] < 0) 
		 $lngSign = 1;
	$absdlng = abs(round($_REQUEST['lngDeg'] * 1000000.));
	$absmlng = abs(round($_REQUEST['lngMin'] * 1000000.)/1000000);
	$absslng = abs(round($_REQUEST['lngSeg'] * 1000000.)/1000000);
	
	$absmlng = abs(round($absmlng * 1000000.));
	$absslng = abs(round($absslng * 1000000.)); 

	
	$latitude = round($absdlat + ($absmlat/60.) + ($absslat/3600.) ) * $latSign/1000000;
	$longitude = round($absdlng + ($absmlng/60.) + ($absslng/3600.) ) * $lngSign/1000000;
	
	$DB->StartTrans(); // start transaction
	
	$strSQL = "UPDATE users SET ";
	$strSQL .= "	userName = '".mb_strtoupper($_REQUEST["userName"], "UTF-8")."', ";
	if(!empty($_REQUEST["userPassword"]))
		$strSQL .= "	userPassword = '".md5($_REQUEST["userPassword"])."', ";
	$strSQL .= "	userCompleteName = '".mb_strtoupper($_REQUEST["userCompleteName"], "UTF-8")."', ";
	$strSQL .= "	userEMail = '".$_REQUEST["userEMail"]."', ";
	$strSQL .= "	userPhone = '".mb_strtoupper($_REQUEST["userPhone"], "UTF-8")."', ";
	$strSQL .= "	userCellPhone = '".mb_strtoupper($_REQUEST["userCellPhone"], "UTF-8")."', ";
	$strSQL .= "	userAddress = '".mb_strtoupper($_REQUEST["userAddress"], "UTF-8")."', ";
	$strSQL .= "	userLocalidad = '".mb_strtoupper($_REQUEST["userLocalidad"], "UTF-8")."', ";
	$strSQL .= "	userState = '".$stateModify."',";
	$strSQL .= "	idTypeUser = ".$_REQUEST["cmbTypeUser"].", ";
	$strSQL .= "	latitude = ".$latitude.", ";
	$strSQL .= "	longitude = ".$longitude;
	$strSQL .= " WHERE idUser = ". $_REQUEST["idUser"];
	
	$DB->Execute($strSQL);
	
	if (!$DB->CompleteTrans())
	{
		$arrJSON->status = "ERROR";
		$arrJSON->msg = 'Ocurri&oacute; un error al grabar el archivo';
	}
	else
	{
		$arrJSON->status = "OK";
		$arrJSON->msg = 'El registro se guard&oacute; correctamente';
	}	
	return json_encode($arrJSON);
}

function del()
{
	require("includes/constants.php");
	
	$DB = NewADOConnection('mysqlt');
	$DB->Connect();
	$DB->Execute("SET NAMES utf8;");
	//$DB->debug=true;
	
	$DB->StartTrans(); // start transaction
	$DB->Execute("UPDATE users SET userState='$stateErase' where idUser=" . $_REQUEST["idUser"]); // execute query

	$arrJSON = new stdClass();
	if (!$DB->CompleteTrans())
	{
		$arrJSON->status = "ERROR";
		$arrJSON->msg = 'Ocurri&oacute; un error al eliminar';
	}
	else
	{
		$arrJSON->status = "OK";
		$arrJSON->msg = 'El registro se elimin&oacute; correctamente';
	}	
	return json_encode($arrJSON);
}

function getTiposUsuario()
{
	$DB = NewADOConnection('mysqlt');
	$DB->Connect();
	$DB->Execute("SET NAMES utf8;");
	//$DB->debug=true;
	
	$rsTypeUsers = $DB->Execute("SELECT * FROM typeuser");
	
	$i=0;
	while(!$rsTypeUsers->EOF){
		$arrTypeUsers->data[$i]['idTypeUser'] = $rsTypeUsers->fields(0);
		$arrTypeUsers->data[$i++]['description'] = $rsTypeUsers->fields(1);
		$rsTypeUsers->MoveNext();
	}
	
	return json_encode($arrTypeUsers->data);
}

function editUser()
{
	require("includes/constants.php");
		
	$DB = NewADOConnection('mysqlt');
	$DB->Connect();
	$DB->Execute("SET NAMES utf8;");
	//$DB->debug=true;
	
	$rsUsers = $DB->Execute("SELECT * FROM users WHERE userState <> '$stateErase' and idUser=" . $_REQUEST["idUser"]); // execute query
						
	if(!$rsUsers->EOF){
		
		$signLat = 1;
		$signLng = 1;
				
		$latAbs = abs(round($rsUsers->fields("latitude") * 1000000.));
		$latDeg = (floor($latAbs / 1000000) * $signLat);		
		$latMin = floor((($latAbs/1000000) - floor($latAbs/1000000)) * 60);
		$latSeg = (floor((((($latAbs/1000000) - floor($latAbs/1000000)) * 60) - floor((($latAbs/1000000) - floor($latAbs/1000000)) * 60)) * 100000) *60/100000);
		
		$lngAbs = abs(round($rsUsers->fields("longitude") * 1000000.));
		$lngDeg = (floor($lngAbs / 1000000) * $signLat);		
		$lngMin = floor((($lngAbs/1000000) - floor($lngAbs/1000000)) * 60);
		$lngSeg = (floor((((($lngAbs/1000000) - floor($lngAbs/1000000)) * 60) - floor((($lngAbs/1000000) - floor($lngAbs/1000000)) * 60)) * 100000) *60/100000);
		
		$joUser->data['userId'] = $rsUsers->fields("idUser");
		$joUser->data['userName'] = $rsUsers->fields("userName");
		$joUser->data['userCompleteName'] = $rsUsers->fields("userCompleteName");
		$joUser->data['userEMail'] = $rsUsers->fields("userEMail");
		$joUser->data['userPhone'] = $rsUsers->fields("userPhone");
		$joUser->data['userAddress'] = $rsUsers->fields("userAddress");
		$joUser->data['userLocalidad'] = $rsUsers->fields("userLocalidad");
		$joUser->data['idTypeUser'] = $rsUsers->fields("idTypeUser");
		$joUser->data['idRestaurant'] = $rsUsers->fields("idRestaurant");
		$joUser->data['latDeg'] = $latDeg;
		$joUser->data['latMin'] = $latMin;
		$joUser->data['latSeg'] = (int)$latSeg;
		$joUser->data['lngDeg'] = $lngDeg;
		$joUser->data['lngMin'] = $lngMin;
		$joUser->data['lngSeg'] = (int)$lngSeg;
		
		return json_encode($joUser->data);
	}
	else
		return json_encode('');
}

function batchFileUploaded()
{
	require("includes/constants.php");
	require("includes/dbf_class.php");
		
	$DB = NewADOConnection('mysqlt');
	$DB->Connect();
	$DB->Execute("SET NAMES utf8;");
	//$DB->debug=true;
	
	
	try
	{
		//Abro el DBF a Procesar
		$dbf = new dbf_class('import/'.$_REQUEST["fileName"]);
		
		//Columnas a Validar
		$colValidate = array('NUMERO', 'NOMBRE', 'DOMI', 'LOCA', 'TELE', 'GLATI', 'MLATI', 'SLATI', 'GLONGI', 'MLONGI', 'SLONGI');
		$dbfOK = TRUE;
		foreach($colValidate as $value)
		{
			if(!in_array($value, $dbf->dbf_col_names))
			{
				$dbfOK = FALSE;
				break;
			}
		}
			
		if(!$dbfOK)
		{
			$arrJSON->status = "ERROR";
			$arrJSON->msg = 'Ocurri&oacute; un error al Importar los Usuarios, DBF Invalido';
			return json_encode($arrJSON);
		}
		
		$num_rec=$dbf->dbf_num_rec;
		
	  	for ($iLoop = 0; $iLoop < $num_rec; $iLoop++) 
	  	{
	  		$rowData = $dbf->getRowByName($iLoop);
			
			$userName = strpos($rowData["NOMBRE"], " ") == 0 ? $rowData["NOMBRE"] : substr($rowData["NOMBRE"], 0, strpos($rowData["NOMBRE"], " "));
			
			$strSQL = "SELECT * FROM users WHERE idUser = ".$rowData["NUMERO"];
			$rsUsers = $DB->Execute($strSQL);

			if($rsUsers->EOF)
			{
		  		$strSQL = "INSERT INTO users ( ";
				$strSQL .= "	idUser, ";
				$strSQL .= "	userName, ";
				$strSQL .= "	userPassword, ";
				$strSQL .= "	userCompleteName, ";
				$strSQL .= "	userPhone, ";
				$strSQL .= "	userAddress, ";
				$strSQL .= "	userLocalidad, ";
				$strSQL .= "	userState, ";
				$strSQL .= "	idTypeUser, ";
				$strSQL .= "	latitude, ";
				$strSQL .= "	longitude ";
				$strSQL .= "	) VALUES ( ";
				$strSQL .= "	".$rowData["NUMERO"].", ";
				$strSQL .= "	'".mb_strtoupper(substr($userName, 0, 15) , "UTF-8")."', ";
				$strSQL .= "	'".md5("1234")."', ";
				$strSQL .= "	'".mb_strtoupper($rowData["NOMBRE"], "UTF-8")."', ";
				$strSQL .= "	'".mb_strtoupper($rowData["TELE"], "UTF-8")."', ";
				$strSQL .= "	'".mb_strtoupper($rowData["DOMI"], "UTF-8")."', ";
				$strSQL .= "	'".mb_strtoupper($rowData["LOCA"], "UTF-8")."', ";
				$strSQL .= "	'".$stateAdd."', ";
				$strSQL .= "	".$idTypeUser.", ";
				$strSQL .= "	". ($rowData["GLATI"] + ($rowData["MLATI"]/60) + ($rowData["SLATI"]/3600)) .", ";
				$strSQL .= "	". ($rowData["GLONGI"] + ($rowData["MLONGI"]/60) + ($rowData["SLONGI"]/3600));
				$strSQL .= "	)";
		  	}
			else {
				$strSQL = "UPDATE users SET ";
				$strSQL .= "	userCompleteName = '".mb_strtoupper($rowData["NOMBRE"], "UTF-8")."', ";
				$strSQL .= "	userPhone = '".mb_strtoupper($rowData["TELE"], "UTF-8")."', ";
				$strSQL .= "	userAddress = '".mb_strtoupper($rowData["DOMI"], "UTF-8")."', ";
				$strSQL .= "	userLocalidad = '".mb_strtoupper($rowData["LOCA"], "UTF-8")."', ";
				$strSQL .= "	latitude = ". ($rowData["GLATI"] + ($rowData["MLATI"]/60) + ($rowData["SLATI"]/3600)) .", ";
				$strSQL .= "	longitude = ". ($rowData["GLONGI"] + ($rowData["MLONGI"]/60) + ($rowData["SLONGI"]/3600));
				$strSQL .= " WHERE idUser = ". $rowData["NUMERO"];
			}
			
			$DB->Execute($strSQL);
		}

		$arrJSON->status = "OK";
		$arrJSON->msg = 'Los Usuarios se Importaron con Exito';
		return json_encode($arrJSON);
	}
	catch(Exception $e) {
		$arrJSON->status = "ERROR";
		$arrJSON->msg = 'Ocurri&oacute; un error al Importar los Usuarios';
		return json_encode($arrJSON);
	}
}

function grabarConfig()
{
	require("includes/constants.php");
	
	$DB = NewADOConnection('mysqlt');
	$DB->Connect();
	$DB->Execute("SET NAMES utf8;");
	//$DB->debug=true;
	
	$DB->StartTrans(); // start transaction
	
	$rsConfig = $DB->Execute("SELECT * FROM config WHERE anno = '".$_REQUEST['anno']."'"); // execute query
	
	if(!$rsConfig->EOF){
		$strSQL = "UPDATE config SET ";
		$strSQL .= "	cantPalomares = '".$_REQUEST['canPalomares']."', ";
		$strSQL .= "	cantPalomaresFondo = '".$_REQUEST['canPalomaresFondo']."', ";
		$strSQL .= "	puntosPorcentaje = '".$_REQUEST['porPuntos']."' ";
		$strSQL .= " WHERE anno = '".$_REQUEST['anno']."'";
	}
	else {
		$strSQL = "INSERT INTO config ( ";
		$strSQL .= "	anno, ";
		$strSQL .= "	cantPalomares, ";
		$strSQL .= "	cantPalomaresFondo, ";
		$strSQL .= "	puntosPorcentaje ";
		$strSQL .= "	) VALUES ( ";
		$strSQL .= "	'".$_REQUEST['anno']."', ";
		$strSQL .= "	'".$_REQUEST['canPalomares']."', ";
		$strSQL .= "	'".$_REQUEST['canPalomaresFondo']."', ";
		$strSQL .= "	'".$_REQUEST['porPuntos']."' ";
		$strSQL .= "	)";
	}
	
	$DB->Execute($strSQL);
	
	
	if (!$DB->CompleteTrans())
	{
		$arrJSON->status = "ERROR";
		$arrJSON->msg = 'Ocurri&oacute; un error al Grabal la Configuraci&oacute;n';
	}
	else
	{
		$arrJSON->status = "OK";
		$arrJSON->msg = 'La Configuraci&oacute;n se grabo correctamente';
	}
	return json_encode($arrJSON);
}

function getConfig()
{
	require("includes/constants.php");
	
	$DB = NewADOConnection('mysqlt');
	$DB->Connect();
	$DB->Execute("SET NAMES utf8;");
	//$DB->debug=true;
	
	$DB->StartTrans(); // start transaction
	
	$rsConfig = $DB->Execute("SELECT * FROM config WHERE anno = '".date('Y')."'"); // execute query
	
	if(!$rsConfig->EOF){
		$joConfig->data['anno'] = $rsConfig->fields("anno");
		$joConfig->data['canPalomares'] = $rsConfig->fields("cantPalomares");
		$joConfig->data['canPalomaresFondo'] = $rsConfig->fields("cantPalomaresFondo");
		$joConfig->data['porPuntos'] = $rsConfig->fields("puntosPorcentaje");
	}
	else {
		$joConfig->data['anno'] = date('Y');
		$joConfig->data['canPalomares'] = "";
		$joConfig->data['canPalomaresFondo'] = "";
		$joConfig->data['porPuntos'] = "";
	}
	
	return json_encode($joConfig->data);
}

switch($_REQUEST['actionOfForm'])
{
	case "EDIT":
		echo editUser();
		break;
	case "DELETE":
		echo del();
		break;
	case "search":
		echo searchABMUsers();
		break;
	case "ADD":
		echo addUser();
		break;
	case "UPDATE":
		echo updateUser();
		break;		
	case "getTiposUsuario":
		echo getTiposUsuario();
		break;
	case "getConfig":
		echo getConfig();
		break;
	case "grabarConfig":
		echo grabarConfig();
		break;
	case "UPLOAD":
		echo batchFileUploaded();
		break;
}
?>
